﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Collections;
using System.Globalization;


/// <summary>
/// EventDAO class is the main class which interacts with the database. SQL Server express edition
/// has been used.
/// the event information is stored in a table named 'event' in the database.
///
/// Here is the table format:
/// event(event_id int, title varchar(100), description varchar(200),event_start datetime, event_end datetime)
/// event_id is the primary key
/// </summary>
public class EventDAO
{
    //change the connection string as per your database connection.
    //private static string connectionString = "server=localhost;User Id=root;Password=root;database=workorganizer";
    static string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

    //this method retrieves all events within range start-end
    public static List<CalendarEvent> getEvents(DateTime start, DateTime end,int level,string userName)
    {
        List<CalendarEvent> events = new List<CalendarEvent>();
        MySqlConnection con = new MySqlConnection(connectionString);
        //MySqlCommand cmd = new MySqlCommand("SELECT event_id, description, title,companyname,groupname,username, event_start, event_end FROM event where event_start>=@start AND event_end<=@end", con);
        MySqlCommand cmd = new MySqlCommand("SELECT e.event_id, e.description, e.title,e.companyname,e.groupname,e.username, e.event_start, e.event_end FROM event e, usertypes ut where e.groupname = ut.usertype AND event_start>=@start AND event_end<=@end AND (ut.level > " + level + " || e.username like '%"+userName+"%')", con);
        cmd.Parameters.AddWithValue("@start", start);
        cmd.Parameters.AddWithValue("@end", end);

        using (con)
        {
            con.Open();
            MySqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                CalendarEvent cevent = new CalendarEvent();
                cevent.id = (int)reader["event_id"];
                cevent.title = (string)reader["title"];
                cevent.description = (string)reader["description"];
                cevent.start = (DateTime)reader["event_start"];
                cevent.end = (DateTime)reader["event_end"];
                cevent.companyname = reader["companyname"] != DBNull.Value ? (string)reader["companyname"] : "";
                cevent.groupname = reader["groupname"] != DBNull.Value ? (string)reader["groupname"] : "";
                string user = reader["username"] != DBNull.Value ? (string)reader["username"] : "";
                string[] users = user.Split(',');
                cevent.users = users;
                events.Add(cevent);
            }
        }
        return events;
        //side note: if you want to show events only related to particular users,
        //if user id of that user is stored in session as Session["userid"]
        //the event table also contains a extra field named 'user_id' to mark the event for that particular user
        //then you can modify the SQL as:
        //SELECT event_id, description, title, event_start, event_end FROM event where user_id=@user_id AND event_start>=@start AND event_end<=@end
        //then add paramter as:cmd.Parameters.AddWithValue("@user_id", HttpContext.Current.Session["userid"]);
    }


    public static List<CalendarEvent> getDaysEvents(DateTime start, int level, string userName)
    {
        List<CalendarEvent> events = new List<CalendarEvent>();
        MySqlConnection con = new MySqlConnection(connectionString);
        //MySqlCommand cmd = new MySqlCommand("SELECT event_id, description, title,companyname,groupname,username, event_start, event_end FROM event where event_start>=@start AND event_end<=@end", con);
        MySqlCommand cmd = new MySqlCommand("SELECT e.event_id, e.description, e.title,e.companyname,e.groupname,e.username, e.event_start, e.event_end FROM event e, usertypes ut where e.groupname = ut.usertype AND date(event_start) = date(@start) AND (ut.level > " + level + " || e.username like '%" + userName + "%')", con);
        cmd.Parameters.AddWithValue("@start", start);

        using (con)
        {
            con.Open();
            MySqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                CalendarEvent cevent = new CalendarEvent();
                cevent.id = (int)reader["event_id"];
                cevent.title = (string)reader["title"];
                cevent.description = (string)reader["description"];
                cevent.start = (DateTime)reader["event_start"];
                cevent.end = (DateTime)reader["event_end"];
                cevent.companyname = reader["companyname"] != DBNull.Value ? (string)reader["companyname"] : "";
                cevent.groupname = reader["groupname"] != DBNull.Value ? (string)reader["groupname"] : "";
                string user = reader["username"] != DBNull.Value ? (string)reader["username"] : "";
                string[] users = user.Split(',');
                cevent.users = users;
                events.Add(cevent);
            }
        }
        return events;
        //side note: if you want to show events only related to particular users,
        //if user id of that user is stored in session as Session["userid"]
        //the event table also contains a extra field named 'user_id' to mark the event for that particular user
        //then you can modify the SQL as:
        //SELECT event_id, description, title, event_start, event_end FROM event where user_id=@user_id AND event_start>=@start AND event_end<=@end
        //then add paramter as:cmd.Parameters.AddWithValue("@user_id", HttpContext.Current.Session["userid"]);
    }

    public static List<CalendarEvent> getEventsByDate(DateTime startDate)
    {
        string dt = startDate.Date.Day.ToString();
        string mn = startDate.Month.ToString();
        string yr = startDate.Year.ToString();

        if (dt.Length < 2)
            dt = "0" + dt.ToString();
        if (mn.Length < 2)
            mn = "0" + mn.ToString();

        string finalDate = dt + "-" + mn + "-" + yr + " 12:00:00 AM";
        string s = yr + "-" + mn + "-" + dt;
        DateTime start = DateTime.ParseExact(finalDate, "dd-MM-yyyy hh:mm:ss tt", CultureInfo.InvariantCulture);
            
        List<CalendarEvent> events = new List<CalendarEvent>();
        MySqlConnection con = new MySqlConnection(connectionString);
        MySqlCommand cmd = new MySqlCommand("SELECT e.event_id, e.description, e.title,e.companyname,e.groupname,e.username, e.event_start, e.event_end FROM event e where Date(e.event_start)=@start", con);
        cmd.Parameters.AddWithValue("@start", s);

        using (con)
        {
            con.Open();
            MySqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                CalendarEvent cevent = new CalendarEvent();
                cevent.id = (int)reader["event_id"];
                cevent.title = (string)reader["title"];
                cevent.description = (string)reader["description"];
                cevent.start = (DateTime)reader["event_start"];
                cevent.end = (DateTime)reader["event_end"];
                cevent.companyname = reader["companyname"] != DBNull.Value ? (string)reader["companyname"] : "";
                cevent.groupname = reader["groupname"] != DBNull.Value ? (string)reader["groupname"] : "";
                string user = reader["username"] != DBNull.Value ? (string)reader["username"] : "";
                string[] users = user.Split(',');
                cevent.users = users;
                events.Add(cevent);
            }
        }
        return events;
    }

    public static ArrayList getUsersForAGroup(string groupName)
    {
        List<CalendarEvent> events = new List<CalendarEvent>();
        MySqlConnection con = new MySqlConnection(connectionString);
        MySqlCommand cmd = new MySqlCommand("SELECT Users.LoginName FROM Users LEFT JOIN UserTypes ON Users.UserTypeId = UserTypes.UserTypeId Where UserTypes.UserType = '" + groupName + "'", con);
        ArrayList a = new ArrayList();
        using (con)
        {
            con.Open();
            MySqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                a.Add(reader["LoginName"] != DBNull.Value ? (string)reader["LoginName"] : "");
            }
        }
        return a;
    }

    public static DataTable getCustomerRelatedFields(string custName)
    {
        MySqlConnection con = new MySqlConnection(connectionString);
        MySqlCommand cmd = new MySqlCommand("SELECT name,address,email,contact_person,contact_number FROM Customers Where name= '" + custName+ "'", con);
        DataTable dt = new DataTable();
        using (con)
        {
            con.Open();
            MySqlDataReader reader = cmd.ExecuteReader();
            dt.Columns.Add("name", typeof(string));
            dt.Columns.Add("address", typeof(string));
            dt.Columns.Add("email", typeof(string));
            dt.Columns.Add("contact_person", typeof(string));
            dt.Columns.Add("contact_number", typeof(string));

            while (reader.Read())
            {
                dt.Rows.Add((string)reader["name"], (string)reader["address"], (string)reader["email"], (string)reader["contact_person"], (string)reader["contact_number"]);
            }
        }
        return dt;
    }

    //this method retrieves all events within range start-end
    public static ArrayList getUsers()
    {
        List<CalendarEvent> events = new List<CalendarEvent>();
        MySqlConnection con = new MySqlConnection(connectionString);
        MySqlCommand cmd = new MySqlCommand("SELECT loginname FROM users", con);
        ArrayList a = new ArrayList();
        using (con)
        {
            con.Open();
            MySqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                a.Add(reader["LoginName"] != DBNull.Value ? (string)reader["LoginName"] : "");
            }
        }
        return a;
    }

    public static ArrayList getGroups(int userTypeId, int level)
    {
        List<CalendarEvent> events = new List<CalendarEvent>();
        MySqlConnection con = new MySqlConnection(connectionString);
        MySqlCommand cmd = new MySqlCommand("SELECT usertype FROM usertypes where level > "+level +" || usertypeid = "+userTypeId, con);
        ArrayList a = new ArrayList();
        using (con)
        {
            con.Open();
            MySqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                a.Add(reader["UserType"] != DBNull.Value ? (string)reader["UserType"] : "");
            }
        }
        return a;
    }

    public static ArrayList getCustomerNames()
    {
        MySqlConnection con = new MySqlConnection(connectionString);
        MySqlCommand cmd = new MySqlCommand("SELECT name FROM customers", con);
        ArrayList a = new ArrayList();
        using (con)
        {
            con.Open();
            MySqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                a.Add(reader["name"] != DBNull.Value ? (string)reader["name"] : "");
            }
        }
        return a;
    }

    public static DataTable getUsersAppointments(string username, int level)
    {
        List<CalendarEvent> events = new List<CalendarEvent>();
        MySqlConnection con = new MySqlConnection(connectionString);
        MySqlCommand cmd = new MySqlCommand("SELECT e.title, e.companyname,e.groupname,e.username, e.event_start, e.event_end FROM event e, usertypes ut where e.groupname = ut.usertype AND (ut.level > " + level + " || e.username like '%" + username + "%')", con);
        DataTable dt = new DataTable();
        using (con)
        {
            con.Open();
            MySqlDataReader reader = cmd.ExecuteReader();
            dt.Columns.Add("title", typeof(string));
            dt.Columns.Add("companyname", typeof(string));
            dt.Columns.Add("start", typeof(DateTime));
            dt.Columns.Add("end", typeof(DateTime));
            dt.Columns.Add("groupname", typeof(string));
            dt.Columns.Add("username", typeof(string));
     
            while (reader.Read())
            {
                dt.Rows.Add((reader["title"] == DBNull.Value ? String.Empty : (string)reader["title"]),
                    (reader["companyname"] == DBNull.Value ? String.Empty : (string)reader["companyname"]), 
                    (DateTime)reader["event_start"], 
                    (DateTime)reader["event_end"],
                    (reader["groupname"] == DBNull.Value ? String.Empty : (string)reader["groupname"]),
                    (reader["username"] == DBNull.Value ? String.Empty : (string)reader["username"]));
            }
        }
        return dt;
    }

    public static DataTable getCustomers()
    {
        MySqlConnection con = new MySqlConnection(connectionString);
        MySqlCommand cmd = new MySqlCommand("SELECT id,name, address,email,contact_person,contact_number FROM customers ", con);
        DataTable dt = new DataTable();
        using (con)
        {
            con.Open();
            MySqlDataReader reader = cmd.ExecuteReader();
            dt.Columns.Add("id", typeof(uint)); 
            dt.Columns.Add("name", typeof(string));
            dt.Columns.Add("address", typeof(string));
            dt.Columns.Add("email", typeof(string));
            dt.Columns.Add("contact_person", typeof(string));
            dt.Columns.Add("contact_number", typeof(string));

            while (reader.Read())
            {
                dt.Rows.Add((uint)reader["id"],(string)reader["name"], (string)reader["address"], (string)reader["email"], (string)reader["contact_person"], (string)reader["contact_number"]);
            }
        }
        return dt;
    }

    public static DataTable getDatesAppointments(string username, int level, DateTime start, DateTime end)
    {
        List<CalendarEvent> events = new List<CalendarEvent>();
        MySqlConnection con = new MySqlConnection(connectionString);
        MySqlCommand cmd = new MySqlCommand("SELECT e.title, e.companyname,e.groupname,e.username, e.event_start, e.event_end FROM event e, usertypes ut where e.groupname = ut.usertype AND event_start>=@start AND event_end<=@end AND (ut.level > " + level + " || e.username like '%" + username + "%')", con);
        cmd.Parameters.AddWithValue("@start", start);
        cmd.Parameters.AddWithValue("@end", end);

        DataTable dt = new DataTable();
        using (con)
        {
            con.Open();
            MySqlDataReader reader = cmd.ExecuteReader();
            dt.Columns.Add("title", typeof(string));
            dt.Columns.Add("companyname", typeof(string));
            dt.Columns.Add("start", typeof(DateTime));
            dt.Columns.Add("end", typeof(DateTime));
            dt.Columns.Add("groupname", typeof(string));
            dt.Columns.Add("username", typeof(string));

            while (reader.Read())
            {
                dt.Rows.Add((string)reader["title"], (string)reader["companyname"], (DateTime)reader["event_start"], (DateTime)reader["event_end"], (string)reader["groupname"], (string)reader["username"]);
            }
        }
        return dt;
    }

    public static DataTable getGroupsAppointments(string username, int level,string groupname)
    {
        List<CalendarEvent> events = new List<CalendarEvent>();
        MySqlConnection con = new MySqlConnection(connectionString);
        MySqlCommand cmd = new MySqlCommand("SELECT e.title, e.companyname,e.groupname,e.username, e.event_start, e.event_end FROM event e, usertypes ut where e.groupname = ut.usertype AND groupname=@groupname AND (ut.level > " + level + " || e.username like '%" + username + "%')", con);
        cmd.Parameters.AddWithValue("@groupname", groupname);

        DataTable dt = new DataTable();
        using (con)
        {
            con.Open();
            MySqlDataReader reader = cmd.ExecuteReader();
            dt.Columns.Add("title", typeof(string));
            dt.Columns.Add("companyname", typeof(string));
            dt.Columns.Add("start", typeof(DateTime));
            dt.Columns.Add("end", typeof(DateTime));
            dt.Columns.Add("groupname", typeof(string));
            dt.Columns.Add("username", typeof(string));

            while (reader.Read())
            {
                dt.Rows.Add((string)reader["title"], (string)reader["companyname"], (DateTime)reader["event_start"], (DateTime)reader["event_end"], (string)reader["groupname"], (string)reader["username"]);
            }
        }
        return dt;
    }

    public static DataTable getOnlyUsersAppointments(string username, int level)
    {
        List<CalendarEvent> events = new List<CalendarEvent>();
        MySqlConnection con = new MySqlConnection(connectionString);
        MySqlCommand cmd = new MySqlCommand("SELECT e.title, e.companyname,e.groupname,e.username, e.event_start, e.event_end FROM event e, usertypes ut where e.groupname = ut.usertype AND e.username like '%" + username + "%'", con);

        DataTable dt = new DataTable();
        using (con)
        {
            con.Open();
            MySqlDataReader reader = cmd.ExecuteReader();
            dt.Columns.Add("title", typeof(string));
            dt.Columns.Add("companyname", typeof(string));
            dt.Columns.Add("start", typeof(DateTime));
            dt.Columns.Add("end", typeof(DateTime));
            dt.Columns.Add("groupname", typeof(string));
            dt.Columns.Add("username", typeof(string));

            while (reader.Read())
            {
                dt.Rows.Add((string)reader["title"], (string)reader["companyname"], (DateTime)reader["event_start"], (DateTime)reader["event_end"], (string)reader["groupname"], (string)reader["username"]);
            }
        }
        return dt;
    }
    
    //this method updates the event title and description
    public static void updateEvent(int id, String title, String description, String companyname, String groupname, string[] selectedusers, DateTime start, DateTime end)
    {
        string users = "";
        //add event to the database and return the primary key of the added event row
        for (int i = 0; i < selectedusers.Length; i++)
        {
            users = users + selectedusers[i].ToString() + ",";

        }
        users = users.TrimEnd(',');

        MySqlConnection con = new MySqlConnection(connectionString);
        MySqlCommand cmd = new MySqlCommand("UPDATE event SET title=@title, description=@description,companyname=@companyname,groupname = @groupname, username=@username, event_start =@event_start, event_end = @event_end  WHERE event_id=@event_id", con);
        cmd.Parameters.AddWithValue("@title", title);
        cmd.Parameters.AddWithValue("@description", description);
        cmd.Parameters.AddWithValue("@companyname", companyname);
        cmd.Parameters.AddWithValue("@groupname", groupname);
        cmd.Parameters.AddWithValue("@username", users);
        cmd.Parameters.AddWithValue("@event_start", start);
        cmd.Parameters.AddWithValue("@event_end", end);
        cmd.Parameters.AddWithValue("@event_id", id);
        using (con)
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
    }

    //this method updates the event start and end time
    public static void updateEventTime(int id, DateTime start, DateTime end)
    {
        MySqlConnection con = new MySqlConnection(connectionString);
        MySqlCommand cmd = new MySqlCommand("UPDATE event SET event_start=@event_start, event_end=@event_end WHERE event_id=@event_id", con);
        cmd.Parameters.AddWithValue("@event_start", start);
        cmd.Parameters.AddWithValue("@event_end", end);
        cmd.Parameters.AddWithValue("@event_id", id);
        using (con)
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
    }

    //this mehtod deletes event with the id passed in.
    public static void deleteEvent(int id)
    {
        MySqlConnection con = new MySqlConnection(connectionString);
        MySqlCommand cmd = new MySqlCommand("DELETE FROM event WHERE (event_id = @event_id)", con);
        cmd.Parameters.AddWithValue("@event_id", id);
        using (con)
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
    }

    //this method adds events to the database
    public static int addEvent(CalendarEvent cevent)
    {
        int key = 0;

        string users = "";
        //add event to the database and return the primary key of the added event row
        for (int i = 0; i < cevent.users.Length; i++)
        {
            users = users + cevent.users[i].ToString() + ",";

        }
        users = users.TrimEnd(',');

        if (cevent.companyname == null)
            cevent.companyname = "";
        if (cevent.groupname == null)
            cevent.groupname = "";
        if (cevent.description == null)
            cevent.description = "";

        //insert
        MySqlConnection con = new MySqlConnection(connectionString);
        MySqlCommand cmd = new MySqlCommand("INSERT INTO event(title, description,companyname,groupname,username, event_start, event_end) VALUES(@title, @description,@companyname,@groupname,@username, @event_start, @event_end)", con);
        cmd.Parameters.AddWithValue("@title", cevent.title);
        cmd.Parameters.AddWithValue("@description", cevent.description);
        cmd.Parameters.AddWithValue("@companyname", cevent.companyname);
        cmd.Parameters.AddWithValue("@groupname", cevent.groupname);
        cmd.Parameters.AddWithValue("@username", users);
        cmd.Parameters.AddWithValue("@event_start", cevent.start);
        cmd.Parameters.AddWithValue("@event_end", cevent.end);

        using (con)
        {
            con.Open();
            cmd.ExecuteNonQuery();

            //get primary key of inserted row
            cmd = new MySqlCommand("SELECT max(event_id) FROM event where title=@title AND description=@description AND event_start=@event_start AND event_end=@event_end", con);
            cmd.Parameters.AddWithValue("@title", cevent.title);
            cmd.Parameters.AddWithValue("@description", cevent.description);
            cmd.Parameters.AddWithValue("@event_start", cevent.start);
            cmd.Parameters.AddWithValue("@event_end", cevent.end);

            key = (int)cmd.ExecuteScalar();
        }
        //}
        return key;
    }

    public static User login(string loginName, string password)
    {
        User user = null;
        try
        {
            MySqlConnection con = new MySqlConnection(connectionString);
            con.Open();
            MySqlCommand cmd = new MySqlCommand("SELECT UserId FROM users where LoginName=@LoginName AND Password=@Password", con);
            cmd.Parameters.AddWithValue("@LoginName", loginName);
            cmd.Parameters.AddWithValue("@Password", password);
            int userId = 0;
            object obj = cmd.ExecuteScalar();
            userId = Convert.ToInt32(obj);
            if (userId != 0)
            {
                user = getUserById(userId);

                if (user != null && user.userId != 0)
                    return user;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return user;
    }

    public static User forgotPass(string emailId)
    {
        User user = new User();
        try
        {
            MySqlConnection con = new MySqlConnection(connectionString);
            con.Open();
            MySqlCommand cmd = new MySqlCommand("SELECT UserId FROM users where EmailId=@EmailId", con);
            cmd.Parameters.AddWithValue("@EmailId", emailId);

            int userId = 0;
            object obj = cmd.ExecuteScalar();
            userId = Convert.ToInt32(obj);
            if (userId != 0)
            {
                user = getUserById(userId);
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return user;
    }

    public static bool isValidUser(string loginname)
    {
        bool isPresent = false;

        try
        {
            MySqlConnection con = new MySqlConnection(connectionString);
            con.Open();
            MySqlCommand cmd = new MySqlCommand("SELECT UserId FROM users where LoginName=@LoginName", con);
            cmd.Parameters.AddWithValue("@LoginName", loginname);

            int key = 0;
            object obj = cmd.ExecuteScalar();
            key = Convert.ToInt32(obj);
            if (key != 0)
                isPresent = true;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return isPresent;
    }

    public static bool isUserPresent(User user)
    {
        bool isPresent = false;

        try
        {
            MySqlConnection con = new MySqlConnection(connectionString);
            con.Open();
            MySqlCommand cmd = new MySqlCommand("SELECT UserId FROM users where LoginName=@LoginName OR MobileNumber=@MobileNumber OR EmailId=@EmailId", con);
            //cmd.Parameters.AddWithValue("@UserTypeid", user.userTypeId);
            cmd.Parameters.AddWithValue("@LoginName", user.loginName);
            cmd.Parameters.AddWithValue("@MobileNumber", user.mobileNumber);
            cmd.Parameters.AddWithValue("@EmailId", user.emailId);

            int key = 0;
            object obj = cmd.ExecuteScalar();
            key = Convert.ToInt32(obj);
            if (key != 0)
                isPresent = true;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return isPresent;
    }

    public static int addUser(User user)
    {
        int key = 0;

        try
        {
            //add event to the database and return the primary key of the added event row
            //insert
            MySqlConnection con = new MySqlConnection(connectionString);
            MySqlCommand cmd = new MySqlCommand("insert into users (  UserTypeId ,  UserName ,  LoginName , Password , MobileNumber , EmailId , CreatedDate , CreatedBy ,ModifiedBy ,ModifiedDate) values (@UserTypeId, @UserName,@LoginName, @Password,@MobileNumber, @EmailId, @CreatedDate, @CreatedBy, @ModifiedBy, @ModifiedDate)", con);
            cmd.Parameters.AddWithValue("@UserTypeid", user.userTypeId);
            cmd.Parameters.AddWithValue("@UserName", user.userName);
            cmd.Parameters.AddWithValue("@LoginName", user.loginName.ToLower());
            cmd.Parameters.AddWithValue("@Password", user.password);
            cmd.Parameters.AddWithValue("@MobileNumber", user.mobileNumber);
            cmd.Parameters.AddWithValue("@EmailId", user.emailId);
            cmd.Parameters.AddWithValue("@CreatedDate", user.createdDate);
            cmd.Parameters.AddWithValue("@CreatedBy", user.createdBy);
            cmd.Parameters.AddWithValue("@ModifiedDate", user.modifiedDate);
            cmd.Parameters.AddWithValue("@ModifiedBy", user.modifiedBy);


            using (con)
            {
                con.Open();
                cmd.ExecuteNonQuery();

                //get primary key of inserted row
                cmd = new MySqlCommand("SELECT max(UserId) FROM users where UserTypeid=@UserTypeid AND LoginName=@LoginName AND MobileNumber=@MobileNumber AND EmailId=@EmailId ", con);
                cmd.Parameters.AddWithValue("@UserTypeid", user.userTypeId);
                cmd.Parameters.AddWithValue("@LoginName", user.loginName);
                cmd.Parameters.AddWithValue("@MobileNumber", user.mobileNumber);
                cmd.Parameters.AddWithValue("@EmailId", user.emailId);

                object obj = cmd.ExecuteScalar();
                key = Convert.ToInt32(obj);
            }

        }
        catch (Exception ex)
        {
            throw ex;
        }
        return key;
    }

    public static int addCustomer(Customer customer)
    {
        int key = 0;

        try
        {
            //add event to the database and return the primary key of the added event row
            //insert
            MySqlConnection con = new MySqlConnection(connectionString);
            MySqlCommand cmd = new MySqlCommand("insert into customers(  name,address,email,contact_person,contact_number) values (@Name, @Address,@Email, @ContactPerson,@ContactNumber)", con);
            cmd.Parameters.AddWithValue("@Name", customer.name);
            cmd.Parameters.AddWithValue("@Address", customer.address);
            cmd.Parameters.AddWithValue("@Email", customer.email);
            cmd.Parameters.AddWithValue("@ContactPerson", customer.contact_person);
            cmd.Parameters.AddWithValue("@ContactNumber", customer.contact_number);

            using (con)
            {
                con.Open();
                cmd.ExecuteNonQuery();

                //get primary key of inserted row
                cmd = new MySqlCommand("SELECT max(id) FROM customers where name=@Name", con);
                cmd.Parameters.AddWithValue("@Name", customer.name);

                object obj = cmd.ExecuteScalar();
                key = Convert.ToInt32(obj);
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return key;
    }

    public static void deleteCustomer(int custId)
    {
        try
        {
            MySqlConnection con = new MySqlConnection(connectionString);
            con.Open();
            MySqlCommand cmd = new MySqlCommand("Delete from customers where id=@Id", con);
            cmd.Parameters.AddWithValue("@Id", custId);
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public static int updateCustomer(Customer customer, int custid)
    {
        int key = 0;

        try
        {
            MySqlConnection con = new MySqlConnection(connectionString);
            MySqlCommand cmd = new MySqlCommand("update customers set name = @Name,address = @Address,email = @Email, contact_person = @ContactPerson,contact_number = @ContactNumber where id = @Id", con);
            cmd.Parameters.AddWithValue("@Id", custid);
            cmd.Parameters.AddWithValue("@Name", customer.name);
            cmd.Parameters.AddWithValue("@Address", customer.address);
            cmd.Parameters.AddWithValue("@Email", customer.email);
            cmd.Parameters.AddWithValue("@ContactPerson", customer.contact_person);
            cmd.Parameters.AddWithValue("@ContactNumber", customer.contact_number);

            using (con)
            {
                con.Open();
                cmd.ExecuteNonQuery();

                cmd = new MySqlCommand("SELECT id FROM customers where id=@Id", con);
                cmd.Parameters.AddWithValue("@Id", custid);

                object obj = cmd.ExecuteScalar();
                key = Convert.ToInt32(obj);
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return key;
    }

    public static User getUserById(int userId)
    {
        DataSet ds = new DataSet();
        MySqlConnection con = new MySqlConnection(connectionString);
        User user = new User();

        using (con)
        {
            con.Open();
            MySqlCommand cmd = new MySqlCommand("select UserId,UserTypeId,UserName,Password,LoginName,MobileNumber,EmailId,CreatedDate,CreatedBy,ModifiedBy,ModifiedDate from users where UserId=@UserId", con);
            cmd.Parameters.AddWithValue("@UserId", userId);

            MySqlDataAdapter adap = new MySqlDataAdapter(cmd);
            adap.Fill(ds);

            DataRow dr = ds.Tables[0].Rows[0];
            user.userId = Convert.ToInt32(dr[0]);
            user.userTypeId = Convert.ToInt32(dr[1]);
            user.userType = getUserTypeById(user.userTypeId);
            user.userName = Convert.ToString(dr[2]);
            user.password = Convert.ToString(dr[3]);
            user.loginName = Convert.ToString(dr[4]);
            user.mobileNumber = Convert.ToString(dr[5]);
            user.emailId = Convert.ToString(dr[6]);
            user.createdDate = Convert.ToDateTime(dr[7]);
            user.createdBy = Convert.ToInt32(dr[8]);
            user.modifiedBy = Convert.ToInt32(dr[9]);
            user.modifiedDate = Convert.ToDateTime(dr[10]);

            MySqlCommand cmd1 = new MySqlCommand("select level from usertypes where UserTypeId=@UserTypeId", con);
            cmd1.Parameters.AddWithValue("@UserTypeId", user.userTypeId);
            object level = cmd1.ExecuteScalar();

            user.level = Convert.ToInt32(level);
        }

        return user;

    }

    public static User getUserByName(string name)
    {
        DataSet ds = new DataSet();
        MySqlConnection con = new MySqlConnection(connectionString);
        User user = new User();

        using (con)
        {
            con.Open();
            MySqlCommand cmd = new MySqlCommand("select UserId,UserTypeId,UserName,Password,LoginName,MobileNumber,EmailId,CreatedDate,CreatedBy,ModifiedBy,ModifiedDate from users where UserName=@UserName", con);
            cmd.Parameters.AddWithValue("@UserName", name);

            MySqlDataAdapter adap = new MySqlDataAdapter(cmd);
            adap.Fill(ds);

            DataRow dr = ds.Tables[0].Rows[0];
            user.userId = Convert.ToInt32(dr[0]);
            user.userTypeId = Convert.ToInt32(dr[1]);
            user.userType = getUserTypeById(user.userTypeId);
            user.userName = Convert.ToString(dr[2]);
            user.password = Convert.ToString(dr[3]);
            user.loginName = Convert.ToString(dr[4]);
            user.mobileNumber = Convert.ToString(dr[5]);
            user.emailId = Convert.ToString(dr[6]);
            user.createdDate = Convert.ToDateTime(dr[7]);
            user.createdBy = Convert.ToInt32(dr[8]);
            user.modifiedBy = Convert.ToInt32(dr[9]);
            user.modifiedDate = Convert.ToDateTime(dr[10]);
        }

        return user;
    }

    public static string getUserTypeById(int userTypeId)
    {
        string key = "";
        MySqlConnection con = new MySqlConnection(connectionString);
        User user = new User();

        using (con)
        {
            con.Open();
            MySqlCommand cmd = new MySqlCommand("select UserType from userTypes where UserTypeId=@UserTypeId", con);
            cmd.Parameters.AddWithValue("@UserTypeId", userTypeId);

            object obj = cmd.ExecuteScalar();
            key = Convert.ToString(obj);
        }

        return key;

    }

    public static bool checkUserTypeExists(string name)
    {
        bool userTypeExists = false;
        MySqlConnection con = new MySqlConnection(connectionString);
        User user = new User();

        using (con)
        {
            con.Open();
            MySqlCommand cmd = new MySqlCommand("select UserTypeId from userTypes where UserType=@UserType", con);
            cmd.Parameters.AddWithValue("@UserType", name);

            object obj = cmd.ExecuteScalar();
            int id = Convert.ToInt32(obj);
            if (id != 0)
                userTypeExists = true;
        }

        return userTypeExists;

    }

    public static bool checkCustomerExists(string name)
    {
        bool customerExists = false;
        MySqlConnection con = new MySqlConnection(connectionString);
        //Customer customer = new Customer();

        using (con)
        {
            con.Open();
            MySqlCommand cmd = new MySqlCommand("select id from customers where name=@Name", con);
            cmd.Parameters.AddWithValue("@Name", name);

            object obj = cmd.ExecuteScalar();
            int id = Convert.ToInt32(obj);
            if (id != 0)
                customerExists = true;
        }

        return customerExists;

    }

    public static int addUserType(UserType userType)
    {
        int key = 0;

        try
        {
            //add event to the database and return the primary key of the added event row
            //insert
            MySqlConnection con = new MySqlConnection(connectionString);
            MySqlCommand cmd = new MySqlCommand("insert into usertypes (UserType, CreatedDate, Description,Parent,level) values (@UserType,@CreatedDate, @Description, @Parent, @level)", con);
            cmd.Parameters.AddWithValue("@UserType", userType.userType);
            cmd.Parameters.AddWithValue("@CreatedDate", userType.createdDate);
            cmd.Parameters.AddWithValue("@Description", userType.desc);
            cmd.Parameters.AddWithValue("@Parent", userType.parent);
            cmd.Parameters.AddWithValue("@level", userType.level);

            using (con)
            {
                con.Open();
                cmd.ExecuteNonQuery();

                //get primary key of inserted row
                cmd = new MySqlCommand("SELECT max(UserTypeId) FROM usertypes where UserType=@UserType AND Description=@Description ", con);
                cmd.Parameters.AddWithValue("@UserType", userType.userType);
                cmd.Parameters.AddWithValue("@Description", userType.desc);

                object obj = cmd.ExecuteScalar();
                key = Convert.ToInt32(obj);
            }

        }
        catch (Exception ex)
        {
            throw ex;
        }
        return key;
    }

    public static int addLeave(int userId, string leaveTitle, string leaveDesc, string from, string to)
    {
        int key = 0;

        try
        {
            //add event to the database and return the primary key of the added event row
            //insert
            MySqlConnection con = new MySqlConnection(connectionString);
            MySqlCommand cmd = new MySqlCommand("insert into leaves (  UserId ,  LeaveTitle,  Reason, FromDate, ToDate , CreatedDate ) values (@UserId ,  @LeaveTitle,  @Reason, @FromDate, @ToDate , @CreatedDate)", con);
            cmd.Parameters.AddWithValue("@Userid", userId);
            cmd.Parameters.AddWithValue("@LeaveTitle", leaveTitle);
            cmd.Parameters.AddWithValue("@Reason", leaveDesc);
            cmd.Parameters.AddWithValue("@FromDate", Convert.ToDateTime(from));
            cmd.Parameters.AddWithValue("@ToDate", Convert.ToDateTime(to));
            cmd.Parameters.AddWithValue("@CreatedDate", DateTime.Now);

            using (con)
            {
                con.Open();
                cmd.ExecuteNonQuery();

                //get primary key of inserted row
                cmd = new MySqlCommand("SELECT max(LeaveId) FROM leaves where UserId=@UserId AND LeaveTitle=@LeaveTitle AND Reason=@Reason AND FromDate=@FromDate AND ToDate=@ToDate ", con);
                cmd.Parameters.AddWithValue("@Userid", userId);
                cmd.Parameters.AddWithValue("@LeaveTitle", leaveTitle);
                cmd.Parameters.AddWithValue("@Reason", leaveDesc);
                cmd.Parameters.AddWithValue("@FromDate", Convert.ToDateTime(from));
                cmd.Parameters.AddWithValue("@ToDate", Convert.ToDateTime(to));

                object obj = cmd.ExecuteScalar();
                key = Convert.ToInt32(obj);
            }

        }
        catch (Exception ex)
        {
            throw ex;
        }
        return key;
    }


    public static int checkIfAnyUserIsOnLeave(string user, DateTime from, DateTime to)
    {
        int key = 0;

        try
        {
            User u = getUserByName(user);
            if (u != null)
            {
                int userId = u.userId;

                MySqlConnection con = new MySqlConnection(connectionString);
                MySqlCommand cmd = new MySqlCommand("SELECT UserId FROM leaves where UserId=@UserId AND (FromDate<=@FromDate AND @FromDate <=ToDate)", con);
                cmd.Parameters.AddWithValue("@Userid", userId);
                cmd.Parameters.AddWithValue("@FromDate", Convert.ToDateTime(from));
                cmd.Parameters.AddWithValue("@ToDate", Convert.ToDateTime(to));
                con.Open();

                object obj = cmd.ExecuteScalar();
                key = Convert.ToInt32(obj);
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return key;
    }

    public static DataSet BindDDLGroup()
    {
        DataSet ds = new DataSet();
        try
        {
            MySqlConnection con = new MySqlConnection(connectionString);
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = "Select UserTypeId,UserType from UserTypes";
            cmd.Connection = con;
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            da.Fill(ds);

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return ds;
    }

    public static void updateUserPassword(string loginName, string newpass)
    {
        MySqlConnection con = new MySqlConnection(connectionString);
        MySqlCommand cmd = new MySqlCommand("UPDATE users SET Password=@Password WHERE LoginName=@LoginName", con);
        cmd.Parameters.AddWithValue("@Password", newpass);
        cmd.Parameters.AddWithValue("@LoginName", loginName);

        using (con)
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
    }

    public static string truncateCustomer()
    {
        string status = "";

        try
        {
            //insert
            MySqlConnection con = new MySqlConnection(connectionString);

            using (con)
            {

                MySqlCommand cmd = new MySqlCommand("truncate table customers", con);
                con.Open();
                cmd.ExecuteNonQuery();
                status = "success";
            }
        }
        catch (Exception ex)
        {
            status = ex.Message.ToString();
            throw ex;
        }
        return status;
    }


    public static string insertCustomer(Customer customer)
    {
        string status = "";

        try
        {
            //insert
            MySqlConnection con = new MySqlConnection(connectionString);
            
            using (con)
            {
                con.Open();

                MySqlCommand cmd = new MySqlCommand("INSERT INTO customers(name, address,email,contact_person,contact_number) VALUES(@name, @address,@email,@contact_person,@contact_number)", con);
                cmd.Parameters.AddWithValue("@name", customer.name);
                cmd.Parameters.AddWithValue("@address", customer.address);
                cmd.Parameters.AddWithValue("@email", customer.email);
                cmd.Parameters.AddWithValue("@contact_person", customer.contact_person);
                cmd.Parameters.AddWithValue("@contact_number", customer.contact_number);

                cmd.ExecuteNonQuery();
                status = "success";
            }
        }
        catch (Exception ex)
        {
            status = ex.Message.ToString();
            throw ex;
        }
        return status;
    }

}
